package data.ChooseCourseData;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import po.chooseCoursePO.ChooseCoursePO;
import chooseCourseStrategy.ChooseCourseStrategy;
import chooseCourseStrategy.Strategy;
import dataservice.ResultMessage;
import dataservice.chooseCourseDataService.ChooseCourseDatabaseService;

public class ChooseCourseData extends UnicastRemoteObject implements
		ChooseCourseDatabaseService {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public ChooseCourseData() throws RemoteException {
		super();
	}

	@Override
	public ResultMessage insert(ChooseCoursePO po) throws RemoteException {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String head = "INSERT INTO `选课管理系统`.`choosecourse`";
			String colomnString = "(`stuID`,`couID`,`score`,`term`)";
			String dataString = "VALUES (" + getValue(po.getStudentId());
			dataString = dataString + "," + getValue(po.getCourseId());
			dataString = dataString + "," + "'"
					+ Double.toString(po.getScore()) + "'";
			dataString = dataString + "," + "'"
					+ Integer.toString(po.getTerm()) + "')";
			String sqlString = head + colomnString + dataString;
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1062) {
				return ResultMessage.INSERT_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1364) {
				return ResultMessage.INSERT_ISNULL;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.INSERT_SUCCESS;
		} else {
			return ResultMessage.INSERT_FAILURE;
		}
	}

	@Override
	public ResultMessage delete(ChooseCoursePO po) throws RemoteException {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "DELETE FROM `选课管理系统`.`choosecourse` "
					+ "WHERE `stuID` = '" + po.getStudentId()
					+ "' AND `couID`='" + po.getCourseId() + "'";
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.DELETE_SUCCESS;
		} else {
			return ResultMessage.DELETE_FAILURE;
		}
	}

	@Override
	public ResultMessage update(ChooseCoursePO po) throws RemoteException {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "UPDATE `选课管理系统`.`choosecourse` SET `score` = "
					+ Double.toString(po.getScore());
			sqlString = sqlString + "," + "`term` = "
					+ Integer.toString(po.getTerm());
			sqlString = sqlString + " WHERE `stuID` = '" + po.getStudentId()
					+ "' AND `couID`='" + po.getCourseId() + "'";
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1452) {
				return ResultMessage.UPDATE_FOREIGNKEY_NOT_EXIST;
			}
			if (e.getErrorCode() == 1062) {
				return ResultMessage.UPDATE_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1048) {
				return ResultMessage.UPDATE_ISNULL;
			}
			if (e.getErrorCode() == 1406) {
				return ResultMessage.UPDATE_TOO_LONG;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.UPDATE_SUCCESS;
		} else {
			return ResultMessage.UPDATE_FAILURE;
		}
	}

	@Override
	public double getScore(ChooseCoursePO po) throws RemoteException {
		double score = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "SELECT choosecourse.score FROM `选课管理系统`.`choosecourse` "
					+ "WHERE (choosecourse.couID='"
					+ po.getCourseId()
					+ "' AND choosecourse.stuID='" + po.getStudentId() + "')";
			ResultSet rs = statement.executeQuery(sqlString);
			if (rs.next()) {
				score = rs.getDouble(1);
			} else {
				score = -1;
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return score;
	}

	@Override
	public ArrayList<ChooseCoursePO> findStuCourse(String stuid)
			throws RemoteException {
		ArrayList<ChooseCoursePO> chooseCoursePOs = new ArrayList<ChooseCoursePO>();

		String sqlString = "SELECT choosecourse.*,course.couName,course.credit,student.stuName,course.attribute FROM choosecourse,course,student "
				+ "WHERE (choosecourse.stuID='"
				+ stuid
				+ "' AND student.stuID=choosecourse.stuID AND course.couID=choosecourse.couID)";
		System.out.println(sqlString);
		chooseCoursePOs = changeToChooseCoursePO(sqlString);
		return chooseCoursePOs;
	}

	@Override
	public ArrayList<ChooseCoursePO> findTeaCourse(String courseid)
			throws RemoteException {
		ArrayList<ChooseCoursePO> chooseCoursePOs = new ArrayList<ChooseCoursePO>();
		String sqlString = "SELECT choosecourse.*,course.couName,course.credit,student.stuName,course.attribute FROM choosecourse,course,student "
				+ "WHERE (choosecourse.couID='"
				+ courseid
				+ "' AND student.stuID=choosecourse.stuID AND course.couID=choosecourse.couID)";
		System.out.println(sqlString);
		chooseCoursePOs = changeToChooseCoursePO(sqlString);
		return chooseCoursePOs;
	}

	private String getValue(String valueString) {
		String result = null;
		if (!((valueString == null) || (valueString.isEmpty()))) {
			result = "'" + valueString + "'";
		} else {
			result = "NULL";
		}
		return result;
	}

	private ArrayList<ChooseCoursePO> changeToChooseCoursePO(String sqlString) {
		ArrayList<ChooseCoursePO> chooseCoursePOs = new ArrayList<ChooseCoursePO>();
		String stuID = null;
		String couID = null;
		double score = 0;
		String stuName = null;
		String couName = null;
		int credit = 0;
		int term = 0;
		String attribute = null;
		ChooseCoursePO chooseCoursePO = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sqlString);
			while (rs.next()) {
				stuID = rs.getString(1);
				couID = rs.getString(2);
				score = rs.getDouble(3);
				term = rs.getInt(4);
				couName = rs.getString(5);
				credit = rs.getInt(6);
				stuName = rs.getString(7);
				attribute = rs.getString(8);
				chooseCoursePO = new ChooseCoursePO(stuID, stuName, couID,
						couName, score, credit, term, attribute);
				chooseCoursePOs.add(chooseCoursePO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return chooseCoursePOs;
	}

	@Override
	public ArrayList<ChooseCoursePO> findChooseCourseByTerm(int term)
			throws RemoteException {
		ArrayList<ChooseCoursePO> chooseCoursePOs = new ArrayList<ChooseCoursePO>();
		String sqlString = "SELECT choosecourse.*,course.couName,course.credit,student.stuName,course.attribute FROM choosecourse,course,student "
				+ "WHERE (choosecourse.term='"
				+ Integer.toString(term)
				+ "' AND student.stuID=choosecourse.stuID AND course.couID=choosecourse.couID)";
		System.out.println(sqlString);
		chooseCoursePOs = changeToChooseCoursePO(sqlString);
		return chooseCoursePOs;
	}

	@Override
	public ResultMessage insertTemp(ChooseCoursePO po) throws RemoteException {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String head = "INSERT INTO `选课管理系统`.`choosecoursetemp`";
			String colomnString = "(`stuID`,`couID`,`score`,`term`)";
			String dataString = "VALUES (" + getValue(po.getStudentId());
			dataString = dataString + "," + getValue(po.getCourseId());
			dataString = dataString + "," + "'"
					+ Double.toString(po.getScore()) + "'";
			dataString = dataString + "," + "'"
					+ Integer.toString(po.getTerm()) + "')";
			String sqlString = head + colomnString + dataString;
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1062) {
				return ResultMessage.INSERT_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1364) {
				return ResultMessage.INSERT_ISNULL;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.INSERT_SUCCESS;
		} else {
			return ResultMessage.INSERT_FAILURE;
		}
	}

	@Override
	public ResultMessage deleteTemp(ChooseCoursePO po) throws RemoteException {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "DELETE FROM `选课管理系统`.`choosecoursetemp` "
					+ "WHERE `stuID` = '" + po.getStudentId()
					+ "' AND `couID`='" + po.getCourseId() + "'";
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.DELETE_SUCCESS;
		} else {
			return ResultMessage.DELETE_FAILURE;
		}
	}

	@Override
	public ArrayList<ChooseCoursePO> findStuCourseTemp(String stuid)
			throws RemoteException {
		ArrayList<ChooseCoursePO> chooseCoursePOs = new ArrayList<ChooseCoursePO>();

		String sqlString = "SELECT choosecoursetemp.*,course.couName,course.credit,student.stuName,course.attribute FROM choosecoursetemp,course,student "
				+ "WHERE (choosecoursetemp.stuID='"
				+ stuid
				+ "' AND student.stuID=choosecoursetemp.stuID AND course.couID=choosecoursetemp.couID)";
		System.out.println(sqlString);
		chooseCoursePOs = changeToChooseCoursePO(sqlString);
		return chooseCoursePOs;
	}

	@Override
	public ArrayList<ChooseCoursePO> findTeaCourseTemp(String courseid)
			throws RemoteException {
		ArrayList<ChooseCoursePO> chooseCoursePOs = new ArrayList<ChooseCoursePO>();
		String sqlString = "SELECT choosecoursetemp.*,course.couName,course.credit,student.stuName,course.attribute FROM choosecoursetemp,course,student "
				+ "WHERE (choosecoursetemp.couID='"
				+ courseid
				+ "' AND student.stuID=choosecoursetemp.stuID AND course.couID=choosecoursetemp.couID)";
		System.out.println(sqlString);
		chooseCoursePOs = changeToChooseCoursePO(sqlString);
		return chooseCoursePOs;
	}

	@Override
	public ArrayList<ChooseCoursePO> findChooseCourseByTermTemp(int term)
			throws RemoteException {
		ArrayList<ChooseCoursePO> chooseCoursePOs = new ArrayList<ChooseCoursePO>();
		String sqlString = "SELECT choosecoursetemp.*,course.couName,course.credit,student.stuName,course.attribute FROM choosecoursetemp,course,student "
				+ "WHERE (choosecoursetemp.term='"
				+ term
				+ "' AND student.stuID=choosecoursetemp.stuID AND course.couID=choosecoursetemp.couID)";
		System.out.println(sqlString);
		chooseCoursePOs = changeToChooseCoursePO(sqlString);
		return chooseCoursePOs;
	}

	@Override
	public ArrayList<ChooseCoursePO> findChooseCourseByIDAndTerm(String studentID,
			int term) throws RemoteException {
		ArrayList<ChooseCoursePO> chooseCoursePOs = new ArrayList<ChooseCoursePO>();
		String sqlString = "SELECT choosecourse.*,course.couName,course.credit,student.stuName,course.attribute FROM choosecourse,course,student "
				+ "WHERE (choosecourse.term='"
				+ term
				+ "' AND choosecourse.stuID='"
				+ studentID
				+ "' AND student.stuID=choosecourse.stuID AND course.couID=choosecourse.couID)";
		System.out.println(sqlString);
		chooseCoursePOs = changeToChooseCoursePO(sqlString);
		return chooseCoursePOs;
	}

	@Override
	public ArrayList<ChooseCoursePO> findAllChooseCourseTemp()
			throws RemoteException {
		ArrayList<ChooseCoursePO> chooseCoursePOs = new ArrayList<ChooseCoursePO>();
		String sqlString = "SELECT choosecoursetemp.*,course.couName,course.credit,student.stuName,course.attribute FROM (choosecoursetemp LEFT JOIN course ON (choosecoursetemp.`couID`=course.`couID`) LEFT JOIN student ON (choosecoursetemp.`stuID`=student.`stuID`) )";
		System.out.println(sqlString);
		chooseCoursePOs = changeToChooseCoursePO(sqlString);
		return chooseCoursePOs;
	}

	@Override
	public ArrayList<ChooseCoursePO> sortChooseCourse(){
		ArrayList<ChooseCoursePO> chooseCoursePOs=null;
		ArrayList<ChooseCoursePO> chooseCoursePOs2=null;
		try {
			 chooseCoursePOs=findAllChooseCourseTemp();
			 ArrayList<ChooseCoursePO> tempArrayList=new ArrayList<>();
			 for(ChooseCoursePO chooseCoursePO:chooseCoursePOs){
				 tempArrayList.add(chooseCoursePO);
			 }
			 ChooseCourseStrategy chooseCourseStrategy=new Strategy(chooseCoursePOs);
			chooseCoursePOs2=chooseCourseStrategy.getResult();
			for (ChooseCoursePO c:chooseCoursePOs2){
				insert(c);
			}
			for(ChooseCoursePO chooseCoursePO:tempArrayList){
				deleteTemp(chooseCoursePO);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return chooseCoursePOs2;
	}

	@Override
	public ArrayList<ChooseCoursePO> findChooseCourseByIDAndTermTemp(
			String studentID, int term) throws RemoteException {
		ArrayList<ChooseCoursePO> chooseCoursePOs = new ArrayList<ChooseCoursePO>();
		String sqlString = "SELECT choosecoursetemp.*,course.couName,course.credit,student.stuName,course.attribute FROM choosecoursetemp,course,student "
				+ "WHERE (choosecoursetemp.term='"
				+ term
				+ "' AND choosecoursetemp.stuID='"
				+ studentID
				+ "' AND student.stuID=choosecoursetemp.stuID AND course.couID=choosecoursetemp.couID)";
		System.out.println(sqlString);
		chooseCoursePOs = changeToChooseCoursePO(sqlString);
		return chooseCoursePOs;
	}
}
